﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Collections;

namespace ET.Monitor.DAL
{
    public class OrgDAL
    {
        object objJudge = null;
        string strSql = "", strErrMsg = "";
        DataTable dt = new DataTable();

        InvokeWebService iw = new InvokeWebService();
        string rtdbRelationAddress = "";
        string rtdbRealTimeAddress = "";
        string rtdbNameSpace = "";
        public OrgDAL()
        {
            rtdbRelationAddress = ConfigurationManager.ConnectionStrings["RelationURL"].ConnectionString;
            rtdbRealTimeAddress = ConfigurationManager.ConnectionStrings["RealTimeURL"].ConnectionString;
            rtdbNameSpace = ConfigurationManager.ConnectionStrings["NameSpace"].ConnectionString;
        }

        #region 用户下面所有电站

        public DataTable GetUserStation(string userId)
        {
            //strSql = "select o.T_ORGID, b.T_ORGDESC_CN from T_SYS_MEMBERORG o inner join T_BASE_ORG b on o.T_ORGID= b.T_ORGID where o.T_USERID= '" + userId + "' and b.T_ORGID not in (select T_PARENTID from T_BASE_ORG)";
            //组织机构调整，查询修改

            //修改增加省份信息
            strSql = "select bc.T_CITYID,bc.T_CITYDESC,b.T_IMGURL,c.T_CITYID cityID, o.T_ORGID, b.T_PLANTDESC from T_SYS_MEMBERORG o inner join T_INFO_PLANT b on o.T_ORGID= b.T_ORGID join T_BASE_CITY c on b.T_CITYID = c.T_CITYID join T_BASE_CITY bc on c.T_PARENTID = bc.T_CITYID where o.T_USERID= '" + userId + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion















        #region 根据组织机构编码获取组织机构信息
        /// <summary>
        /// 根据组织机构编码获取组织机构信息
        /// 创    建：刘重阳  
        /// 日    期：20150107
        /// </summary>
        /// <param name="oid">组织机构编码</param>
        /// <returns></returns>
        public DataTable GetOrgInfo(string oid)
        {
            strSql = "SELECT T_ORGDESC_CN,T_ORGDESC_EN,T_PARENTID FROM T_BASE_ORG where T_ORGID='" + oid + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region 新增组织机构
        /// <summary>
        /// 新增组织机构
        /// 创    建：刘重阳  
        /// 日    期：20150107
        /// </summary>
        /// <param name="orgId"></param>
        /// <param name="parentId"></param>
        /// <param name="orgNameCn"></param>
        /// <param name="orgNameEn"></param>
        /// <returns></returns>
        public bool AddOrgModel(string orgId, string parentId, string orgNameCn)
        {
            strSql = "INSERT INTO T_BASE_ORG VALUES('" + orgId + "','" + orgNameCn + "','" + parentId + "','');";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool AddPlantDetailInfo(string orgId, string longitude, string lantitude, string installDate, string level1, string level2,
                                        string level3, string electricityPrice, string irradiance, string month1, string month2, string month3,
                                        string month4, string month5, string month6, string month7, string month8, string month9, string month10, string month11, string month12)
        {
            strSql = @"INSERT INTO T_INFO_PLANT (T_ORGID,D_LONG,D_LAT,T_INSTALLDATA,D_LEVEL1,D_LEVEL2,D_LEVEL3,D_ELECTRICITYTARIFF,D_IRRADIANCE,D_TENERGY1,D_TENERGY2,D_TENERGY3,D_TENERGY4,D_TENERGY5,
                                            D_TENERGY6,D_TENERGY7,D_TENERGY8,D_TENERGY9,D_TENERGY10,D_TENERGY11,D_TENERGY12)
                        VALUES('" + orgId + "'," + longitude.ToString() + "," + lantitude + ",'" + installDate + "'," + level1 + "," + level2 + "," + level3 +
                                 "," + electricityPrice + "," + irradiance + "," + month1 + "," + month2 + "," + month3 + "," + month4 + "," + month5 +
                                 "," + month6 + "," + month7 + "," + month8 + "," + month9 + "," + month10 + "," + month11 + "," + month12 + ")";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 根据组织机构编码删除组织机构
        /// <summary>
        /// 根据组织机构编码删除组织机构
        /// 创    建：刘重阳  
        /// 日    期：20150107
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteOrgModelById(string id)
        {
            strSql = "DELETE FROM T_BASE_ORG WHERE T_ORGID='" + id + "'";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool DeletePlantInfo(string id)
        {
            strSql = "DELETE FROM T_INFO_PLANT WHERE T_ORGID='" + id + "'";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 根据组织机构编码修改组织机构信息
        /// <summary>
        /// 根据组织机构编码修改组织机构信息
        /// 创    建：刘重阳  
        /// 日    期：20150107
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateOrgModel(string id, string nameCn)
        {
            strSql = "UPDATE T_BASE_ORG SET T_ORGDESC_CN='" + nameCn + "' WHERE T_ORGID='" + id + "'";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public bool UpdatePlantInfo(string orgId, string longitude, string lantitude, string installDate, string level1, string level2,
                                        string level3, string electricityPrice, string irradiance, string month1, string month2, string month3,
                                        string month4, string month5, string month6, string month7, string month8, string month9, string month10, string month11, string month12)
        {
            strSql = @"UPDATE T_INFO_PLANT SET D_LONG= " + longitude + ",D_LAT=" + lantitude + ",T_INSTALLDATA='" + installDate + "',D_LEVEL1=" + level1 + ",D_LEVEL2=" + level2 +
                        ",D_LEVEL3=" + level3 + ",D_ELECTRICITYTARIFF=" + electricityPrice + ",D_IRRADIANCE=" + irradiance +
                        ",D_TENERGY1=" + month1 + ",D_TENERGY2=" + month2 + ",D_TENERGY3=" + month3 + ",D_TENERGY4=" + month4 + ",D_TENERGY5=" + month5 + ",D_TENERGY6=" + month6 +
                        ",D_TENERGY7=" + month7 + ",D_TENERGY8=" + month8 + ",D_TENERGY9=" + month9 + ",D_TENERGY10=" + month10 + ",D_TENERGY11=" + month11 + ",D_TENERGY12=" + month12 +
                        "WHERE T_ORGID='" + orgId + "'";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion



        #region 获取上一级数据
        public DataTable GetOrgParentInfo(string oid)
        {
            strSql = "select T_ORGID,T_ORGDESC_CN,T_ORGDESC_EN,T_PARENTID from T_BASE_ORG where T_ORGID=(select T_PARENTID from T_BASE_ORG where T_ORGID='" + oid + "')";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region 获取下一级组织信息
        public DataTable GetOrgSunInfo(string oid)
        {
            strSql = "select T_ORGID,T_ORGDESC_CN,T_ORGDESC_EN,T_PARENTID from T_BASE_ORG where T_PARENTID='" + oid + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }

        public DataTable GetOrgSunInfos(string oids)
        {
            strSql = "select T_ORGID,T_ORGDESC_CN,T_ORGDESC_EN,T_PARENTID from T_BASE_ORG where T_PARENTID in(" + oids + ")";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region 获取区域信息
        public DataTable GetOrgAreaInfo(string oid)
        {
            strSql = "select T_ORGID,T_DISTRICTDESC_CN,T_DISTRICTDESC_EN,T_DISTRICTID from T_BASE_DISTRICT where T_ORGID='" + oid + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

        #region 胡进财

        #region 获取组织机构信息 省市
        public DataTable GetCity()
        {
            strSql = "select * from T_BASE_CITY";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }

        #endregion

        #region 获取所有组织机构信息--->左右电站
        public DataTable GetAllOrgInfo()
        {
            //strSql = "select * from T_BASE_ORG";
            //由于组织机构修改，查询更改
            strSql = "select T_ORGID,T_CITYID,T_PLANTDESC,T_PARENTID,D_LONG,D_LAT,D_STARTDATE from T_INFO_PLANT";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }

        public DataTable GetAllOrgStationInfo(string userId)
        {
            //strSql = "select * from T_BASE_ORG";
            //由于组织机构修改，查询更改
            strSql = "select c.T_CITYID,c.T_CITYDESC, o.T_ORGID,b.T_IMGURL, b.T_PLANTDESC,b.T_IMGURL,c.T_PARENTID,b.D_LONG,b.D_LAT,D_STARTDATE from T_SYS_MEMBERORG o " +
"inner join T_INFO_PLANT b on o.T_ORGID = b.T_ORGID and o.T_USERID = '" + userId + "' join T_BASE_CITY c on b.T_CITYID = c.T_CITYID order by c.T_CITYID";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }


        #endregion

        #region 获取某个用户下面的所有组织机构
        public DataTable GetUserAuthorization(string userId)
        {
            strSql = "select * from T_SYS_MEMBERORG where T_UserID='" + userId + "'";
            dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            return dt;
        }
        #endregion

     

        #endregion

        #region 复睿组织机构管理
        public bool AddFRPlantInfo(string id, string cityId, string name, string longitude, string latitude,
                        string createDate, string CompletionDate, string networkDate, string orientation, string angle, string capacity, string month1, string month2, string month3,
                                        string month4, string month5, string month6, string month7, string month8, string month9, string month10, string month11, string month12)
        {
            strSql = @"INSERT INTO T_INFO_PLANT (T_ORGID,T_CITYID,D_LONG,D_LAT,D_STARTDATE,D_ENDDATE,D_CONNECTDATE,T_ORIENTATION,T_ANGLE,D_REPORTCAPCITY,T_PLANTDESC,D_TENERGY1,D_TENERGY2,D_TENERGY3,D_TENERGY4,D_TENERGY5,
                                            D_TENERGY6,D_TENERGY7,D_TENERGY8,D_TENERGY9,D_TENERGY10,D_TENERGY11,D_TENERGY12)
                        VALUES('" + id + "','" + cityId + "'," + longitude + "," + latitude + ",'" + createDate + "','" + CompletionDate + "','"
                            + networkDate + "','" + orientation + "','" + angle + "'," + capacity + ",'" + name + "'," + month1 + "," + month2 + "," + month3 + "," + month4 + "," + month5 +
                                 "," + month6 + "," + month7 + "," + month8 + "," + month9 + "," + month10 + "," + month11 + "," + month12 + ")";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }

        public DataTable GetPlantInfoById(string id)
        {
            strSql = "SELECT T_ORGID,T_PLANTDESC FROM T_INFO_PLANT where T_ORGID='" + id + "'";
            return (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
        }

        public List<string> GetGroupInfoById(string id)
        {
            List<string> l_data = new List<string>();
            strSql = "select ac.T_CITYDESC T_PARENTDESC , ab.T_CITYDESC, ab.T_PLANTDESC from T_BASE_CITY ac inner join ( " +
"select a.T_PLANTDESC, b.T_CITYID, b.T_CITYDESC, b.T_PARENTID from T_INFO_PLANT a inner join T_BASE_CITY b on a.T_CITYID = b.T_CITYID and T_ORGID = '" + id + "') ab on ab.T_PARENTID = ac.T_CITYID";
            DataTable dt = (DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg });
            if (dt != null)
            {
                l_data.Add(dt.Rows[0]["T_PARENTDESC"].ToString());
                l_data.Add(dt.Rows[0]["T_CITYDESC"].ToString());
                l_data.Add(dt.Rows[0]["T_PLANTDESC"].ToString());
            }
            return l_data;
        }



        public bool UpdateFRPlantInfo(string orgId, string name, string longitude, string lantitude, string installDate, string endDate, string workDate,
                                      string orientation, string angle, string capacity, string month1, string month2, string month3,
                                      string month4, string month5, string month6, string month7, string month8, string month9, string month10, string month11, string month12)
        {
            strSql = @"UPDATE T_INFO_PLANT SET T_PLANTDESC='" + name + "', D_LONG=" + longitude + ",D_LAT=" + lantitude + ",D_STARTDATE='" + installDate + "',D_ENDDATE='" + endDate + "',D_CONNECTDATE='" + workDate +
                        "',T_ORIENTATION='" + orientation + "',T_ANGLE='" + angle + "',D_REPORTCAPCITY='" + capacity +
                        "',D_TENERGY1=" + month1 + ",D_TENERGY2=" + month2 + ",D_TENERGY3=" + month3 + ",D_TENERGY4=" + month4 + ",D_TENERGY5=" + month5 + ",D_TENERGY6=" + month6 +
                        ",D_TENERGY7=" + month7 + ",D_TENERGY8=" + month8 + ",D_TENERGY9=" + month9 + ",D_TENERGY10=" + month10 + ",D_TENERGY11=" + month11 + ",D_TENERGY12=" + month12 +
                        " WHERE T_ORGID='" + orgId + "'";
            return (bool)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunNonQuery", new object[] { strSql, strErrMsg });
        }
        #endregion

        #region 获取市信息
        public string GetCity(string stationId)
        {
            strSql = "select T_CITYDESC from T_BASE_CITY where T_CITYID=(select T_CITYID from T_INFO_PLANT where T_ORGID='" + stationId + "')";
            return iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg }).ToString();
        }
        #endregion

        #region 获取省信息
        public string GetPrivince(string stationId)
        {
            strSql = "select distinct T_CITYDESC from T_BASE_CITY where T_CITYID=(select T_PARENTID from T_BASE_CITY where T_CITYID = (select T_CITYID from T_INFO_PLANT where T_ORGID='" + stationId + "'))";
            return iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunSingle", new object[] { strSql, strErrMsg }).ToString();
        }
        #endregion

        #region  获取某个省份下面的城市
        public IList<Hashtable> GetCityByPrivince(string stationId)
        {
            strSql = "select distinct ct.T_CITYDESC,ct.T_CITYID from T_BASE_CITY ct right join T_INFO_PLANT pt on ct.T_CITYID = pt.T_CITYID where ct.T_PARENTID = '" + stationId + "'";
            return DataTableToList((DataTable)iw.RunWebService(rtdbRelationAddress, rtdbNameSpace, "RunDataTable", new object[] { strSql, strErrMsg }));
        }
        #endregion


        #region List 转换
        public IList<Hashtable> DataTableToList(DataTable dt)
        {
            IList<Hashtable> list = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                list = new List<Hashtable>();
                Hashtable ht = null;
                foreach (DataRow row in dt.Rows)
                {
                    ht = new Hashtable();
                    foreach (DataColumn col in dt.Columns)
                    {
                        if (row[col.ColumnName] != null && !string.IsNullOrEmpty(Convert.ToString(row[col.ColumnName])))
                        {
                            ht.Add(col.ColumnName.ToUpper(), row[col.ColumnName]);
                        }
                        else
                        {
                            ht.Add(col.ColumnName.ToUpper(), "");
                        }
                    }
                    list.Add(ht);
                }
            }
            return list;
        }
        #endregion
    }
}
